#coding: utf8
'''
==================================================================
Created on 2021年12月13日 By Simon

==================================================================
'''

import xlrd
import re

tasks = [
    [40, "修复企业信息智能验证的bug"],
    [40, "迁移数据到青白江的服务器并修改对应代码"],
    [59, "服务器维修，服务正常使用"],
    [59, "项目测试和上线政务云"],
    [59, "正式环境适配及数据库迁移"],
    [59, "项目上线测试"],
    [59, "正式环境数据调整更新及系统更新部署"],
    [59, "Jenkins部署上线"],
    [59, "域名小程序备案"],
    [59, "服务器修复，原因排查"],
    [59, "正式环境系统配置改造及部署测试完成"],
    [59, "修复系统，提升服务器配置"],
    [22, "Pc端、小程序端第三轮联动测试，协助bug解决"],
    [22, "回归测试验证已修复bug"],
    [22, "回归测试验证已修复bug23个，新增bug8个"],
    [22, "门户及后台管理系统第一轮测试已修复bug回归测试"],
    [22, "测试数据删除及功能验证"],
    [22, "门户及后台管理系统联动测试发现的bug提交"],
    [22, "验证已修复的bug,激活未修复bug"],
    [22, "Pc端及小程序bug反馈、跟进"],
    [22, "验证后台管理系统已修复bug"],
    [22, "反馈bug,验证已修复bug"],
    [22, "系统热招职位、急招职位审核等模块相关bug提交"],
    [22, "回归测试验证已修复bug25个，新增bug9个"],
    [22, "门户及后台管理系统已开发模块联动测试"],
    [22, "回归测试验证已修复bug36个，新增bug17个"],
    [22, "验证已修复bug7,新增bug5个"],
    [22, "验证系统已修复bug"],
    [22, "回归测试验证已修复bug12个，新增bug11个"],
    [22, "小程序端第一轮测试已修复bug验证、跟进"],
    [22, "门户及后台系统第一轮联动测试"],
    [22, "回归测试验证已修复bug18个，新增bug7个"],
    [22, "Pc端、小程序端联动测试，协助bug解决"],
    [22, "提交门户及后台管理系统bug"],
    [58, "个人用户职位/企业收藏、收藏列表、推荐接口开发"],
    [58, "个人简历信息创建、编辑、信息删除接口开发及自测"],
    [58, "原型业务逻辑梳拟"],
    [58, "门户首页轮播内容展示"],
    [58, "在线沟通消息添加简历id字段及接口调整"],
    [58, "企业招聘中心、找人才等模块测试"],
    [58, "用人单位提交认证/认证信息获取接口开发"],
    [58, "展位模块测试"],
    [58, "系统测试及配合前端调整bug"],
    [58, "个人用户投递简历模块数据库设计及接口开发"],
    [58, "企业发布专场职位线上/线下接口调整完成"],
    [58, "测试方案编写"],
    [58, "门户系统个人简历接口编写"],
    [58, "专场招聘模块测试"],
    [58, "企业找人才、职位推荐人才接口开发自测"],
    [58, "门户在线沟通账户列表、实时沟通io开发及自测"],
    [58, "个人简历模块数据建模"],
    [58, "短信验证码模板申请、短信签名申请完成"],
    [58, "页面数据内容添加"],
    [58, "小程序专场热门城市/最近访问城市列表接口开发"],
    [58, "验收资料测试方案编写"],
    [58, "专场职位/企业列表、企业职位列表接口开发及自测"],
    [58, "门户展位信息展示及展位预约流程开发"],
    [58, "企业展位重新预约接口调整及试用"],
    [58, "用人单位单位主页信息编辑及详情接口开发"],
    [58, "门户招聘专场列表及详情展示"],
    [58, "测试计划设计"],
    [58, "用人单位职位数据库表设计及实体类创建"],
    [58, "测试用例设计"],
    [58, "展位预约、简历投递等模块测试"],
    [58, "阿里云短信服务引入及短信发送测试完成"],
    [58, "门户用人单位职位管理模块接口开发及自测"],
    [58, "完善用人单位信息接口编写"],
    [58, "企业认证委托书模板下载接口开发及自测"],
    [58, "企业/个人用户通知消息列表、标记已读接口开发"],
    [58, "验证招聘中心/专场招聘/展位预约等模块修复bug"],
    [58, "门户/小程序系统第二轮回归测试"],
    [58, "数据库添加数据"],
    [58, "个人简历、投递反馈模块测试"],
    [58, "用户注册登录及个人信息维护"],
    [58, "门户发布职位添加字段及业务流程调整完成"],
    [58, "个人简历接口文档编写"],
    [58, "个人简历数据库表结构调整及实体类创建"],
    [58, "企业用户简历收藏/取消收藏接口开发自测"],
    [58, "系统演示职位/企业数据调整更改"],
    [58, "个人用户职位推荐策略调整修改完成"],
    [58, "个人用户简历信息详情接口开发及自测"],
    [58, "门户访问量统计拦截设计及开发调整完成"],
    [58, "热门职位关键词录入及列表接口开发"],
    [58, "企业用户简历投递列表、简历详情、简历设置接口开发"],
    [58, "企业/个人我的收藏模块测试"],
    [58, "门户资讯内容展示"],
    [58, "第二轮测试提交的已修bug验证"],
    [58, "急招/热招职位列表、热招企业列表接口开发"],
    [58, "企业职位发布模块测试"],
    [58, "用人单位发布日常/急招/专场职位接口开发及自测"],
    [60, "企业简历管理添加最近招聘职位下拉列表接口"],
    [60, "专场招聘添加最近访问城市接口完成"],
    [60, "验收资料《测试计划》编写"],
    [60, "个人、企业用户业务逻辑梳拟"],
    [60, "小程序聊天demo编写"],
    [60, "小程序在线沟通获取账户信息接口修改完成"],
    [60, "验收资料测试方案编写"],
    [60, "企业端企业中心、职位收藏等模块测试"],
    [60, "小程序业务功能测试及配合修改bug"],
    [60, "Ui及兼容测试"],
    [60, "个人我的、消息、找工作等模块测试"],
    [60, "小程序专场热门城市统计添加过滤完成"],
    [60, "企业端首页、通知消息、用人单位中心等模块测试"],
    [60, "企业签到、专场展位预约、简历管理等模块测试"],
    [60, "小程序信息认证材料准备及申请"],
    [60, "展位预约、公司主页等模块冒烟测试"],
    [60, "企业简历查看、面试要求模块测试"]
]


from src.libs.database import Database



'''
其它        22    142
小程序        60    143
数据支持    40    144
运维部署    59    145
门户系统    58    146
'''

    
def get_record(db, table, name):
    sql = "select id, moduleId, {} name from {} where projectId=%s and moduleId in (22, 40, 58, 59, 60)".format(name, table)
    res = db.read_all(sql, (48,))
    '''
    sql = "select id, moduleId, {} name from {} where projectId=%s and moduleId in (142, 143, 144, 145, 146)".format(name, table)
    res = db.read_all(sql, (44,))
    '''
    
    dts = {}
    for item in res:
        _id, m_id, name = item['id'], item['moduleId'], item['name']
        if m_id not in dts: dts[m_id] = {}
        if name not in dts[m_id]:
            dts[m_id][name] = _id
        else: 
            pass
            # print('Error', item)
    return dts


def check_data():
    db = Database()
    '''
    sql = "select id, updateTime from record_day where id in (4324, 6246, 4235, 3236, 3271, 3320, 3357, 3385, 3442, 3514, 3565, 3617, 3645, 3695, 3739, 3791, 3823, 2990, 3041, 4069, 4898, 580, 611, 662, 687, 712, 733, 2099, 2157, 2190, 2219, 2242, 2290, 2354, 2385, 2440, 2481, 2496, 2562, 2603, 2648, 2704, 2714, 2773, 2799, 2826, 2861, 2879, 2895, 2913, 2918, 3001, 3050, 3071, 5918, 5975, 6027, 6121, 6159, 3661, 4750, 4767, 4840, 4746, 4814, 4831, 4628, 2321, 4932, 2323, 2728, 4896, 2433, 2472, 2514, 2539, 2571, 4927, 4815, 4745, 4612, 4256, 4953, 4728, 2633, 2781, 2829, 2843, 2870, 4686, 4613, 4629, 4687, 4729, 1597, 1640, 1690, 1834, 1850, 1890, 1951, 2013, 2212, 1042, 660, 685, 319, 432, 467, 498, 281, 4607, 2512, 2566, 584, 640, 1381, 1478, 1502, 1530, 1689, 1833, 1847, 4244, 4287, 4319, 4381, 1088, 1119, 4606, 466, 499, 532, 1419, 364, 410, 1382, 1501, 1529, 1688, 1888, 1950, 1194, 1259, 1347, 336, 363, 3632, 1337, 4061, 1005, 1046, 1070, 1114, 1006, 1041, 459, 507, 583, 4006, 716, 362, 376, 406, 431, 468, 533, 930, 739, 377, 407, 430, 469, 500, 534, 2467, 2536, 3631, 798, 830, 880, 278, 4933, 1361, 2209, 4605, 4634, 4666, 4700, 1380, 1596, 48, 1532, 1552, 409, 433, 641, 1179, 1193, 4060, 4635, 684, 2296, 4007, 1120, 1178, 1595, 1641, 1687, 1848, 2012, 198, 2779, 2801, 2828, 2844, 2957, 1007, 1594, 1686, 1832, 1887, 1949, 2567, 740, 779, 4320, 4321, 1004, 405, 2268, 2446, 1476, 2569, 2465, 2503, 4005, 2431, 2470, 2430, 2510, 2540, 4637, 2568, 2537, 2591, 2632, 2700, 4062, 2469, 2513, 2432, 2471)"
    sql = "select id, updateTime from plan_day where id in (2193, 2210, 2239, 2259, 2279, 2310, 2354, 2385, 2421, 2441, 2479, 2511, 2514, 2553, 2055, 431, 453, 467, 489, 508, 1458, 1486, 1509, 1537, 1554, 1581, 1616, 1636, 1671, 1695, 1706, 1733, 1743, 1778, 1806, 1843, 1849, 1883, 1902, 1923, 1954, 1972, 1988, 2001, 2006, 2061, 2088, 3969, 4013, 4054, 3211, 3225, 3209, 1687, 1715, 1747, 1793, 1839, 1856, 1889, 1905, 1926, 1939, 1963, 1993, 3108, 3123, 3165, 742, 449, 465, 196, 284, 310, 128, 1712, 406, 1006, 1061, 1076, 1103, 1155, 1301, 2818, 2854, 2881, 2909, 766, 799, 309, 338, 368, 974, 1003, 1033, 186, 231, 1062, 1077, 1102, 1206, 1300, 1310, 1335, 826, 870, 882, 968, 185, 211, 2410, 739, 768, 788, 743, 301, 392, 213, 197, 219, 239, 262, 283, 311, 370, 487, 511, 198, 240, 261, 282, 308, 339, 369, 433, 1686, 2411, 529, 573, 590, 982, 3103, 3128, 3153, 1156, 1107, 264, 827, 871, 464, 828, 1153, 1179, 1205, 1298, 1308, 1377, 55, 1797, 1855, 1904, 1938, 2031, 3052, 717, 1154, 1178, 1204, 1299, 1307, 1334, 1375, 1418, 1497, 1506, 1714, 531, 725, 1563, 1651, 973, 1004, 1032, 1731, 1683, 1495, 1505, 1528, 1595, 1620, 1648, 1665, 1664, 1729, 1768, 1684, 1666)"
    res = db.read_all(sql, [])
    sqls = []
    for item in res:
        sql = 'update record_day set updateTime="{}" where id={}'.format(str(item['updateTime']), item['id'])
        sql = 'update plan_day set updateTime="{}" where id={}'.format(str(item['updateTime']), item['id'])
        print(sql + ";")
        # sqls.append([sql, []])
        
        # print('[', item['id'], ',"', str(item['updateTime']), '"]')
        
    print(len(res))
    return
    '''
    
    dtr = get_record(db, "record_day", "task")
    dtr2 = get_record(db, "record_day_del", "task")
    dtp = get_record(db, "plan_day", "task")
    # dtt = get_record(db, "task", "")
    
    # print(len(dtr))
    # print(len(dtr2))
    # print(len(dtp))
    # print(len(dtt))
    # return
    
    m_ids = {22: 142, 40: 144, 58: 146, 59: 145, 60: 143}
    sqls = []
    # print(dts)
    print(len(tasks))
    
    # return
    
    '''恢复任务表'''
    '''
    dts = dtt
    for _id, name in tasks:
        if name in dts[_id]:
            sql = "update task set moduleId=%s, projectId=44 where id=%s"
            sqls.append([sql, [m_ids[_id], dts[_id][name]]])
    '''
    
    '''恢复日志表'''
    count = 0
    ids = []

    for _id, name in tasks:
        # print(_id, name)
        # if name in dtr[_id]:
        
        # if m_ids[_id] not in dtr2: continue
        if _id not in dtr2: continue 
        
        if name in dtr2[_id]:
            
            sql = 'update record_day_del set moduleId=%s, projectId=44, project=%s where projectId=48 and moduleId=%s and task=%s'
            params = [m_ids[_id], "青白江人才项目", _id, name]
            sqls.append([sql, params])
            
            
            # print(params)
            s = 'select * from record_day_del where projectId=48 and moduleId=%s and task="%s"' % (_id, name)
            s = 'update plan_day set moduleId=%s, projectId=44, project=%s where projectId=48 and moduleId=%s and task=%s' % (m_ids[_id], "青白江人才项目", _id, name)
            # print(s)
            
            
            
            continue
            s = 'select * from plan_day where projectId=44 and moduleId=%s and task="%s"' % (m_ids[_id], name)
            # s = 'select * from record_day where projectId=44 and moduleId=%s and task="%s"' % (m_ids[_id], name)
            
            info = db.read_all(s, [])
            count += len(info)
            if len(info) == 0:
                print(m_ids[_id], name)
                print(s) 
                # print(info)
            
            pass
            for item in info:
                ids.append(item['id']) 
        else:
            pass
            # print(name)
    
    print(ids)
    print(count)
    flag, _ = db.Transaction(sqls)
    print(flag, _)
    
    
def read_data():
    file_path = "C:/Users/Simon/Desktop/20220715113107766.xls"
    data = xlrd.open_workbook(file_path)
    table = data.sheets()[0]
    dts = {}
    for row in range(table.nrows):
        name = table.cell_value(row, 0)
        time = table.cell_value(row, 3)
        content = table.cell_value(row, 5)
        if content.find("青白江") != -1:
            # print(content)
            print("=" * 64)
            '''系统上线时间为2021-10-18'''
            y, m, d, _ = re.split("年|月|日", time)
            if y == '2021' and (int(m) < 10 or (m=='10' and int(d) < 19)): continue 
            proj = split_proj("\n" + content, dts)
            # if proj == True: print(time)
    
    for m in dts:
        print(m)
        for d in dts[m]:
            print("\t", d)

def split_proj(content, dts):
    keys = re.findall("\n(.*?：)", content)
    ps = re.split('|'.join(keys), content)
    blocks = [p.strip() for p in ps if p.strip() != '']
    if len(blocks) != len(keys): print("error", len(blocks), len(keys))
    
    idxs = [i for i, k in enumerate(keys) if k.find("青白江") != -1]
    if len(idxs) == 0: return
    if len(idxs) > 1: print("error")
    
    proj = blocks[idxs[0]]
    
    # if proj.find("视频检测算法研究；") != -1: return True
    lines = proj.split("\n")
    
    for line in lines:
        line = line.strip()
        if line == "": continue
        lp = re.findall("^\d+[、.](.*)，计划", line)
        if len(lp) != 1:
            print("+ Error +" * 16)
            print(line)
            return True
        '''End If'''
        mt = lp[0].split("-")
        m, t = "其它", ""
        if len(mt) == 1:
            t = lp[0]
        elif len(mt) == 2:
            m, t = mt
        elif len(mt) > 2:
            m = mt[0]
            t = lp[0][len(m) + 1:]
        if m not in dts: dts[m] = set()
        dts[m].add(t)
        
        
    
    
    
if __name__ == '__main__':
    # read_data()
    check_data()
    
    pass